package com.sopra.tecnica.poc.server.dao;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.sopra.tecnica.poc.server.entity.OrderEntity;

@Repository
public class OrderDao extends ADao<OrderEntity> {

	@Override
	protected Class<OrderEntity> getManagedClass() {
		return OrderEntity.class;
	}

	public List<OrderEntity> findLastYearCustomerOrders(String customerCode) {
		Calendar calendar = Calendar.getInstance();
		calendar.set(Calendar.YEAR, calendar.get(Calendar.YEAR) - 1);
		String query = getSelect() + " FROM " + getTable() + " WHERE ncliv = '" + customerCode
				+ "' AND YEAR(datcde) >= '" + calendar.get(Calendar.YEAR) + "'";
		System.out.println(query);
		return getJdbcTemplate().query(query, getMapper());
	}

	public List<Map<String, Object>> itemsForReport(int orderNum) {
		return getJdbcTemplate().queryForList(
				"SELECT nart as 'code', desi as 'designation', l.lib as 'color', taille as 'size', q.qtecde as 'orderedQty', remise as 'discount', prixnet as 'netPrice'"
				+ " FROM pub.ligcde l, pub.qtecde q"
				+ " WHERE l.societe = '001' AND l.noccde = ?"
				+ " AND q.societe = l.societe AND q.noccde = l.noccde"
				+ " AND q.noclig = l.noclig AND q.qtecde > 0"
				+ " ORDER BY 'code', 'color', 'size'", orderNum);
	}

	public void updateSumQty(Integer orderNum) {
		int sumQty = getJdbcTemplate().queryForInt(
				"SELECT SUM(qtetot)"
				+ " FROM pub.ligcde"
				+ " WHERE societe = '001'"
				+ " AND noccde = ?"
				+ " GROUP BY noccde",
			    orderNum);
		
		getJdbcTemplate().update(
				"UPDATE " + getTable()
				+ " SET qtetot = ?"
				+ " WHERE societe = '001'"
				+ " AND noccde = ?",
				sumQty, orderNum);
		
		System.out.format(
				"UPDATE " + getTable()
				+ " SET qtetot = %s"
				+ " WHERE societe = '001'"
				+ " AND noccde = %s%n",
				sumQty, orderNum);
	}
	
	public void updateSumPrice(Integer orderNum) {
		BigDecimal sumPrice = getJdbcTemplate().queryForObject(
				"SELECT SUM(mnttot)"
				+ " FROM pub.ligcde"
				+ " WHERE societe = '001'"
				+ " AND noccde = ?"
				+ " GROUP BY noccde",
				BigDecimal.class,
			    orderNum);
		
		getJdbcTemplate().update(
				"UPDATE " + getTable()
				+ " SET mnttot = ?"
				+ " WHERE societe = '001'"
				+ " AND noccde = ?",
				sumPrice, orderNum);
		
		System.out.format(
				"UPDATE " + getTable()
				+ " SET mnttot = %s"
				+ " WHERE societe = '001'"
				+ " AND noccde = %s%n",
				sumPrice, orderNum);
	}
}
